package com.kx.center.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.kx.center.entity.Trade;
import com.kx.center.entity.TradeDetail;
import com.kx.center.entity.huiyin.MtName;
import com.kx.center.entity.huiyin.MtOrder;
import com.kx.center.service.SendEmailService;
import com.kx.center.service.TradeDetailService;
import com.kx.center.service.TradeService;
import com.kx.center.util.DateUtil;
import com.kx.center.util.ExcelUtil;
import com.kx.center.util.Response;
import com.kx.center.util.SQLHelper;

@Controller
@RequestMapping("/data")
public class DataController {

	@Autowired
	TradeService tradeService;
	@Autowired
	TradeDetailService tradeDetailService;
	@Autowired
	SendEmailService sendEmailService;
	
	@RequestMapping(value = "submit", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
	@ResponseBody
	public String company_web_submit(HttpServletRequest request){
		
		String message = request.getParameter("message");
		String name = request.getParameter("name");
		String tel = request.getParameter("tel");
		
		try {
			String template = "websiteTemplate";
			List<String> email = new ArrayList<String>();
			email.add("phil.zhang@4chen.cn");
			Map<String, Object> model = new HashMap<String, Object>();
			model.put("name", name);
			model.put("tel", tel);
			model.put("message", message);
			sendEmailService.sendEmailFromTemplate(template, email, model);
		} catch (Exception e) {
			return Response.success("订货单提交成功，邮件发送失败!");
		}
		
		return Response.success("提交成功!");
	}

	@RequestMapping(value = "upload", method = RequestMethod.POST)
	public String upload(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		MultipartFile multipartFile = multipartRequest.getFile("file");
		String sourceName = multipartFile.getOriginalFilename();
		String base = request.getSession().getServletContext().getRealPath("/")
				+ "attachments" + File.separator + "uploadedExcel";
		File file = new File(base);
		if (!file.exists()) {
			file.mkdirs();
		}
		try {
			String path = base + File.separator + sourceName;
			multipartFile.transferTo(new File(path));
			File read = new File(path);
			InputStream is = new FileInputStream(read);
			HSSFWorkbook hwb = new HSSFWorkbook(is);
			List<MtOrder> list = new ArrayList<MtOrder>();
			for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
				HSSFSheet hs = hwb.getSheetAt(i);
				for (int j = 1; j <= hs.getLastRowNum(); j++) {
					MtOrder mo = new MtOrder();
					HSSFRow hr = hs.getRow(j);
					mo.setOrderSn(ExcelUtil.getValue(hr.getCell(0)));
					mo.setPaymentTime(DateUtil.timestampToUnixTime(ExcelUtil
							.getValue(hr.getCell(1))));
					mo.setStoreName(ExcelUtil.getValue(hr.getCell(3)));
					mo.setStoreId(NumberUtils.toInt((ExcelUtil.getValue(hr
							.getCell(4))).substring(0, 6)));
					mo.setStoreAddress(ExcelUtil.getValue(hr.getCell(5)));
					mo.setIsPhoneorder(ExcelUtil.getValue(hr.getCell(6))
							.equals("在线支付") ? 1 : 2);
					mo.setOrderState(ExcelUtil.getValue(hr.getCell(7)));
					mo.setIsReservation(ExcelUtil.getValue(hr.getCell(9)));
					mo.setGoodsAmount(Double.parseDouble(ExcelUtil.getValue(hr
							.getCell(10))));
					mo.setOrderAmount(Double.parseDouble(ExcelUtil.getValue(hr
							.getCell(11))));
					mo.setMtDisamount(Double.parseDouble(ExcelUtil.getValue(hr
							.getCell(12))));
					mo.setDisAmount(Double.parseDouble(ExcelUtil.getValue(hr
							.getCell(13))));
					mo.setGoodsName(ExcelUtil.getValue(hr.getCell(14)));
					String[] s = mo.getGoodsName().split("/");
					List<MtName> l = new ArrayList<MtName>();
					for (int k = 0; k < s.length; k++) {
						MtName name = new MtName();
						Pattern p = Pattern
								.compile("(.*)\\，单价(.*)\\*数量(\\d+(\\.\\d+)?)\\+餐盒0\\.0\\*数量(.*)");
						Matcher m = p.matcher(s[k]);
						m.matches();
						name.setGoodsName(m.group(1));
						name.setGoodsNum(NumberUtils.toInt(m.group(3)));
						name.setGoodsPrice(NumberUtils.toDouble(m.group(2)));
						l.add(name);
					}
					mo.setNameList(l);
					list.add(mo);
				}
			}

			for (MtOrder mt : list) {
				MtOrder mtOrder = new MtOrder();
				mtOrder.setOrderSn(mt.getOrderSn());
				mtOrder.setGoodsAmount(mt.getGoodsAmount());
				mtOrder.setOrderAmount(mt.getOrderAmount());
				mtOrder.setMtDisamount(mt.getMtDisamount());
				mtOrder.setDisAmount(mt.getDisAmount());
				mtOrder.setIsReservation(mt.getIsReservation());
				mtOrder.setPaymentTime(mt.getPaymentTime());
				mtOrder.setStoreName(mt.getStoreName());
				mtOrder.setStoreId(mt.getStoreId());
				mtOrder.setIsPhoneorder(mt.getIsPhoneorder());
				mtOrder.setStoreAddress(mt.getStoreAddress());
				mtOrder.setOrderState(mt.getOrderState());
				tradeService.insertMtOrder(mtOrder);
			}
			System.out.println("订单上传成功");

			for (MtOrder mt : list) {
				for (MtName n : mt.getNameList()) {
					n.setGoodsPayPrice(n.getGoodsPrice() / mt.getGoodsAmount()
							* (mt.getOrderAmount() + mt.getMtDisamount())
							* n.getGoodsNum());
				}
			}

			int length = list.size();
			for (int i = 0; i < length; i++) {
				MtOrder mtOrder = list.get(i);
				for (MtName n : mtOrder.getNameList()) {
					MtName mtName = new MtName();
					mtName.setOrderId(mtOrder.getOrderSn());
					mtName.setStoreName(mtOrder.getStoreName());
					mtName.setGoodsName(n.getGoodsName());
					mtName.setGoodsNum(n.getGoodsNum());
					mtName.setGoodsPrice(n.getGoodsPrice() * n.getGoodsNum());
					mtName.setGoodsPayPrice(n.getGoodsPayPrice());
					tradeDetailService.insertMtOrderDetail(mtName);
				}
			}
			System.out.println("订单详情上传成功");

			List<Trade> tradeList = tradeService.findMtOrderList();
			for (Trade t : tradeList) {
				Trade trade = new Trade();
				trade.setOuterId(t.getOrderSn());
				trade.setOrderSn(t.getOrderSn());
				trade.setStoreId(t.getStoreId());
				trade.setStoreName(t.getStoreName());
				trade.setAddTime(t.getAddTime());
				trade.setPaymentTime(t.getPaymentTime());
				trade.setGoodsAmount(t.getGoodsAmount());
				trade.setOrderAmount(t.getOrderAmount());
				trade.setDisAmount(t.getDisAmount());
				trade.setChannelId(3);
				tradeService.insertTrade(trade);
			}

			List<TradeDetail> detailList = tradeDetailService
					.findMtDetailList();
			for (TradeDetail td : detailList) {
				TradeDetail tradeDetail = new TradeDetail();
				tradeDetail.setOuterId(td.getOuterId());
				tradeDetail.setGoodsNum(td.getGoodsNum());
				tradeDetail.setGoodsPrice(td.getGoodsPrice());
				tradeDetail.setGoodsPayPrice(td.getGoodsPayPrice());
				tradeDetail.setGoodsName(td.getGoodsName());
				tradeDetail.setGoodsSku(td.getGoodsSku());
				tradeDetail.setBundleSku(td.getBundleSku());
				tradeDetail.setGoodsId(td.getGoodsId());
				tradeDetail.setStoreId(td.getStoreId());
				tradeDetail.setChannelId(3);
				tradeDetailService.insertTradeDetail(tradeDetail);
			}
			System.out.println("成功写入odb");

			// 更新trade_detail的trade_id
			String sql01 = "UPDATE trade_detail t_d JOIN trade t_t ON t_d.outer_id = t_t.outer_id "
					+ "SET t_d.trade_id = t_t.trade_id WHERE t_d.channel_id = 3";
			SQLHelper.executeUpdate(sql01, null, SQLHelper.odb);
			// 更新库存
			String sql02 = "CREATE TABLE IF NOT EXISTS store_goods_temp3 LIKE store_goods ";
			SQLHelper.executeUpdate(sql02, null, SQLHelper.odb);
			// 清空临时表
			String sql03 = "delete from store_goods_temp3";  
			SQLHelper.executeUpdate(sql03, null, SQLHelper.odb);
			// 查询各门店销售额及商品id到临时表中
			String sql04 = "INSERT INTO store_goods_temp3(store_id,goods_id,storage) "
					+ "SELECT t.store_id,t_d.goods_id, SUM(t_d.goods_num) selled_quantity "
					+ " FROM trade t JOIN trade_detail t_d ON t_d.outer_id = t.outer_id "
					+ "WHERE TO_DAYS(FROM_UNIXTIME(t.add_time)) = TO_DAYS(now()) - 1 "
					+ "AND t.channel_id = 3  GROUP BY goods_id,t.store_id ";
			SQLHelper.executeUpdate(sql04, null, SQLHelper.odb);

			// 执行更新
			String sql05 = "UPDATE store_goods s_g JOIN store_goods_temp3 s_g_t "
					+ "ON s_g.goods_id = s_g_t.goods_id AND s_g.store_id = s_g_t.store_id "
					+ "SET s_g.ref_storage = (s_g.storage - s_g_t.storage)";
			SQLHelper.executeUpdate(sql05, null, SQLHelper.odb);
			// 清空临时表
			SQLHelper.executeUpdate(sql03, null, SQLHelper.odb);
			System.out.println("库存更新成功");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "/api/statistical/download";
	}
}
